﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SalePartial.IDAL;
using System.Data.SqlClient;
using SalePartial.Utility;
using SalePartial.Model;
using System.Data;
using XunPan.Web.Globals;

namespace SalePartial.BLL
{
    public class XPInquiryFilter : IXPInquiryFilter
    {

        public int Delete(Guid InquiryFilterID)
        {
            string SqlString = "Delete XPInquiryFilter Where InquiryFilterID = @InquiryFilterID";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.Parameters.AddWithValue("@InquiryFilterID", InquiryFilterID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }

        public int DeleteDynamic(string v_whereCondition)
        {
            string SqlString = "Delete XPInquiryFilter Where " + v_whereCondition;
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }

        public int Insert(XPInquiryFilterInfo info)
        {
            string SqlString = "Insert Into XPInquiryFilter Values "+
                "(@InquiryFilterID,@CompanyID,@FilterName,@CreateDate,@UpdateDate,@FilterExpression,@FilterExpression2,@XPAllCnt,@XPNewCnt)";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;
                        Sql_Command.Parameters.AddWithValue("@InquiryFilterID", info.InquiryFilterID);
                        Sql_Command.Parameters.AddWithValue("@CompanyID", info.CompanyID);
                        Sql_Command.Parameters.AddWithValue("@FilterName", info.FilterName.Trim());
                        Sql_Command.Parameters.AddWithValue("@CreateDate", info.CreateDate);
                        Sql_Command.Parameters.AddWithValue("@UpdateDate", info.UpdateDate);
                        Sql_Command.Parameters.AddWithValue("@FilterExpression", info.FilterExpression.Trim());
                        Sql_Command.Parameters.AddWithValue("@FilterExpression2", info.FilterExpression2);
                        Sql_Command.Parameters.AddWithValue("@XPAllCnt", info.XPAllCnt);
                        Sql_Command.Parameters.AddWithValue("@XPNewCnt", info.XPNewCnt);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }

        public System.Data.DataSet SelectAll()
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet SelectByFilterID(Guid InquiryFilterID)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPInquiryFilter");
            sb.Append("   WHERE ");
            sb.Append("  InquiryFilterID= @InquiryFilterID");

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    Sql_Command.Parameters.AddWithValue("@InquiryFilterID", InquiryFilterID);

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }

        public System.Data.DataSet SelectByCompanyID(Guid CompanyID)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPInquiryFilter");
            sb.Append("   WHERE ");
            sb.Append("  CompanyID= @CompanyID");

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    Sql_Command.Parameters.AddWithValue("@CompanyID", CompanyID);

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }

        public System.Data.DataSet SelectDynamic(string v_whereCondition, string v_orderByExpression)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPInquiryFilter");
            if (v_whereCondition != null && v_whereCondition != string.Empty)
            {
                sb.Append("   WHERE ");
                sb.Append(v_whereCondition);
            }
            if (v_orderByExpression != null && v_orderByExpression != string.Empty)
            {
                sb.Append("   ORDER BY ");
                sb.Append(v_orderByExpression);
            }

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();
                    
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;
        }

        public System.Data.DataSet SelectPaged()
        {
            throw new NotImplementedException();
        }

        public System.Data.SqlClient.SqlDataReader SelectReader(Guid InquiryFilterID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPInquiryFilter");
            sb.Append("   WHERE ");
            sb.Append("  InquiryFilterID= @InquiryFilterID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);
                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@InquiryFilterID", InquiryFilterID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);
               
            }
            catch (Exception e)
            {
                return  null;
            }
        }

        public XPInquiryFilterInfo SelectInfoByFilterID(Guid InquiryFilterID)
        {
            XPInquiryFilterInfo info = null;
           SqlDataReader reader =  SelectReader(InquiryFilterID);
           if (reader == null)
           {
               return null;
           }
           while (reader.Read())
           {
               info = new XPInquiryFilterInfo(reader);

           }
           if (!reader.IsClosed)
           {
               reader.Close();
           }
           return info;
        }


        public System.Data.SqlClient.SqlDataReader SelectReaderByCompanyID(Guid CompanyID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPInquiryFilter");
            sb.Append("   WHERE ");
            sb.Append("  CompanyID= @CompanyID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);
                
                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@CompanyID", CompanyID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);
                
            }
            catch (Exception e)
            {
                return null;
            }
        }


        public int Update(Model.XPInquiryFilterInfo info)
        {
            StringBuilder SqlString = new StringBuilder();
            SqlString.Append("Update  XPInquiryFilter set ");
            SqlString.Append("   FilterName = @FilterName   ");
            SqlString.Append("   , UpdateDate = @UpdateDate ");
            SqlString.Append("   , FilterExpression = @FilterExpression ");
            SqlString.Append("   , FilterExpression2 = @FilterExpression2 ");
            SqlString.Append("   , XPAllCnt = @XPAllCnt ");
            SqlString.Append("   , XPNewCnt = @XPNewCnt ");
            SqlString.Append("   Where InquiryFilterID = @InquiryFilterID ");
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString.ToString();
                        Sql_Command.Parameters.AddWithValue("@InquiryFilterID", info.InquiryFilterID);
                        Sql_Command.Parameters.AddWithValue("@FilterName", info.FilterName);
                        Sql_Command.Parameters.AddWithValue("@UpdateDate", info.UpdateDate);
                        Sql_Command.Parameters.AddWithValue("@FilterExpression", info.FilterExpression);
                        Sql_Command.Parameters.AddWithValue("@FilterExpression2", info.FilterExpression2);
                        Sql_Command.Parameters.AddWithValue("@XPAllCnt", info.XPAllCnt);
                        Sql_Command.Parameters.AddWithValue("@XPNewCnt", info.XPNewCnt);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }


        private int SelectXPCnt(string v_whereCondition)
        {
            DataSet ds = new DataSet();

            string SqlString = "Select COUNT(*) From vw_XPValidInquiry Where " + v_whereCondition;
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        SqlDataAdapter da = new SqlDataAdapter();
                        da.SelectCommand = Sql_Command;

                        da.Fill(ds);

                        if (ds == null)
                        {
                            return 0;
                        }
                        else
                        {
                            int cnt = 0;
                            int.TryParse(ds.Tables[0].Rows[0][0].ToString(), out cnt);
                            return cnt;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

        }



        public bool updateXPFilterCnts(Guid CompanyID)
        {
            SqlDataReader reader = SelectReaderByCompanyID(CompanyID);
            while (reader.Read())
            {
                XPInquiryFilterInfo info = new XPInquiryFilterInfo(reader);

                string expre = GlobalUtility.DeserialzedFromString(info.FilterExpression);

                info.XPAllCnt = SelectXPCnt(expre);

                expre += "AND CreateDate >=" + DateTime.Now.AddDays(-3).ToString("d");

                info.XPNewCnt = SelectXPCnt(expre);

                Update(info);

            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return true;
        }
    }
}
